Oracle SQL*Plus Pocket Reference, 2nd Edition by Jonathan Gennick

Author:Jonathan Gennick [Gennick, Jonathan]
Language: fra
Format: epub
Tags: Informatique
Publisher: O'Reilly
Published: 0101-01-01T00:00:00+00:00

1.5 Updating Data

Use the UPDATE statement to modify column values in existing table rows.

1.5.1 Simple Updates

A simple UPDATE statement takes on the following form:

UPDATE table_name

SET column_name = new_value,

column_name = new_value,

column_name = new_value,

...

WHERE selection_criteria;

For example, the following statement corrects a small problem with a course name; it changes the name to use an "I" (letter) instead of a "1" (digit): UPDATE course

SET course_name = 'Spanish I'

WHERE course_name = 'Spanish 1';

Be careful with updates. If you omit the WHERE clause, your update will be applied to all rows in the table.

1.5.2 Noncorrelated Subqueries in the SET Clause

Rather than specify a new value in the SET clause for a column, you can specify a subquery that returns exactly one value (one column, one row). That value then becomes the new column value.

For example:

UPDATE enrollment

SET period = (

SELECT period

FROM course

WHERE course_name = 'English II'),

course_name = (

SELECT course_name

FROM course

WHERE course_name = 'English II'),

WHERE course_name = 'English II';

Setting the PERIOD and COURSE_NAME columns to their current values by way of a subquery doesn't make much sense. I did it only to show that you can use more than one subquery in an UPDATE statement.

1.5.3 Correlated Subqueries in the SET Clause

Subqueries in UPDATE statements are often more useful when they are correlated. A correlated subquery is one in which the row returned depends on the current row being updated. For example, the following UPDATE statement uses a correlated subquery to reset all periods in the enrollment table to values taken from the COURSE table:

UPDATE enrollment e

SET period = (

SELECT MIN(period)

FROM course c

WHERE c.course_name = e.course_name);

Note the use of the table aliases c and e to qualify the column names in the WHERE clause. I used the MIN function in this case, because some courses (Spanish I, for example) are offered in more than one period.

When using correlated subqueries, you can specify multiple columns in your SET clause; just be sure to enclose them within parentheses:

UPDATE enrollment e

SET (course_name, period) = (

SELECT course_name, period

FROM course c

WHERE c.course_name = e.course_name

AND c.period = e.period);

This UPDATE statement, which serves only as a usage example, essentially uses a subquery to set COURSE_NAME and PERIOD to their current values.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.